﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;

using CNCSoft.EData;
using System.Data.SqlClient;

using CrystalDecisions.CrystalReports.Engine;
using CrystalDecisions.Shared;

using Common;


namespace CNCSoft.ErpApps.TMS.Academic
{
    public partial class frmChuyenLop : Form
    {
        private SqlDataAdapter dataAdapter = new SqlDataAdapter();
        DataTable dt1;
        DataTable dt2;
        DataTable DM_TrangThai;

        public frmChuyenLop()
        {
            InitializeComponent();
        }

        private void frmChuyenLop_Load(object sender, EventArgs e)
        {
            try
            {
                //Combo
                DM_TrangThai = new DataTable();
                SQLHelper.Fill(DM_TrangThai, GlobalVariable.WSInfo.ConnectionString, "SELECT * FROM DM_TRANGTHAI");
                cboTrangThai.DataSource = DM_TrangThai;
                cboTrangThai.DisplayMember = "TenTrangThai";
                cboTrangThai.ValueMember = "MaTrangThai";
                cboTrangThai.SelectedValue = "SS";

                cboHocPhi.Items.Add("Học phí");
                cboHocPhi.Items.Add("Học hè");
                cboHocPhi.SelectedIndex = 0;

                txtMaNienHoc1.Text = txtMaNienHoc2.Text = GlobalVariable.WSInfo.getAppProperty("MaNienHocDefault", "");
                gridColumn_DiemTB1.SortOrder = DevExpress.Data.ColumnSortOrder.Descending;
                gridColumn_DiemTB2.SortOrder = DevExpress.Data.ColumnSortOrder.Descending;

                optInBenTrai.Text = "Trái";
                optInBenPhai.Text = "Phải";
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        private void txtMaNienHoc1_TextChanged(object sender, EventArgs e)
        {
            try
            {
                if (txtMaNienHoc1.Text == "") return;
                DataTable dt = new DataTable();
                SQLHelper.Fill(dt, GlobalVariable.WSInfo.ConnectionString, "SELECT * FROM DM_LOP WHERE MaNienHoc = '" + txtMaNienHoc1.Text + "' Order by MaKhoi, STT");
                cboLop1.DataSource = dt;
                cboLop1.DisplayMember = "TenLop";
                cboLop1.ValueMember = "MaLop";
            }
            catch (Exception ex)
            { MessageBox.Show(ex.Message); }
        }

        private void cboLop1_SelectedIndexChanged(object sender, EventArgs e)
        {
            try
            {
                GetData(1, cboLop1.SelectedValue.ToString());
                optInBenTrai.Text = cboLop1.SelectedValue.ToString(); //.Substring(9) ;
            }
            catch (Exception ex)
            { MessageBox.Show(ex.Message); }
        }


        private void GetData(int intNguon, string strMaLop)  //intNguon= 1/2
        {
            if (strMaLop == "") return;
            string strMaHP = cboHocPhi.Text ;
            //string strTrangThai = "N'%" + cboTrangThai.SelectedValue + "%'";
            string strTrangThai = "";
            if (cboTrangThai.SelectedIndex == -1)
                strTrangThai = "";
            else
                strTrangThai = cboTrangThai.SelectedValue.ToString();

            try
            {
                if (intNguon == 1)
                {
                    dt1 = new DataTable(); 
                    SQLHelper.Fill(dt1, GlobalVariable.WSInfo.ConnectionString, CommandType.StoredProcedure, "sp_frmChuyenLop_LoadDanhSachChuyenLop",
                        new SqlParameter[] { new SqlParameter("@pMaLop", strMaLop), new SqlParameter("@pMaTrangThai", strTrangThai), new SqlParameter("@pMaHocPhi", strMaHP) });
                    dt1.PrimaryKey = new DataColumn[] { dt1.Columns["MaHocTap"] };
                    gridDSLop1.DataSource = dt1;
                }
                else
                {
                    dt2 = new DataTable();
                    SQLHelper.Fill(dt2, GlobalVariable.WSInfo.ConnectionString, CommandType.StoredProcedure, "sp_frmChuyenLop_LoadDanhSachChuyenLop",
                        new SqlParameter[] { new SqlParameter("@pMaLop", strMaLop), new SqlParameter("@pMaTrangThai", strTrangThai), new SqlParameter("@pMaHocPhi", strMaHP) });
                    dt2.PrimaryKey = new DataColumn[] { dt2.Columns["MaHocTap"] };
                    gridDSLop2.DataSource = dt2;
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        private void txtMaNienHoc2_TextChanged(object sender, EventArgs e)
        {
            try
            {
                if (txtMaNienHoc2.Text == "") return;
                DataTable dt = new DataTable();
                SQLHelper.Fill(dt, GlobalVariable.WSInfo.ConnectionString, "SELECT * FROM DM_LOP WHERE MaNienHoc = '" + txtMaNienHoc2.Text + "' Order by MaKhoi, STT");

                cboLop2.DataSource = dt;
                cboLop2.DisplayMember = "TenLop";
                cboLop2.ValueMember = "MaLop";
            }
            catch (Exception ex)
            { MessageBox.Show(ex.Message); }
        }

        private void cboLop2_SelectedIndexChanged(object sender, EventArgs e)
        {
            try
            {
                GetData(2, cboLop2.SelectedValue.ToString());
                optInBenPhai.Text = cboLop2.SelectedValue.ToString(); //.Substring(9)  ;
            }
            catch (Exception ex)
            { MessageBox.Show(ex.Message); }
        }

        private void cmdFrom1To2_Click(object sender, EventArgs e)
        {
            try
            {
                //Chuyen row dang chon tu dt1 sang dt2
                int idx = gridDSLop1.BindingContext[dt1].Position;
                DataRow selectedRow = dt1.Rows[idx];
                DataRow newRow = dt2.NewRow();
                
                newRow.ItemArray = selectedRow.ItemArray; // copy data
                newRow["MaLop"] = cboLop2.SelectedValue;
                newRow["MaLopCu"] = cboLop1.SelectedValue;
                newRow["SetModify"] = "U";
                
                if (optMove.Checked)
                {
                    dt1.Rows.Remove(selectedRow);
                }
                else  //Thay doi MaHocTap (khong la bi trung)
                {
                    try
                    {
                        //Dau tien ưu tiên lấy mã HT = Mã HS + mã Lớp, nếu bị trùng (lỗi PK) thì mới thêm chữ copy.
                        string MaHocTapMoi = selectedRow["MaHocSinh"] + cboLop2.SelectedValue.ToString();
                        if (SQLHelper.ExecuteNonQuery(GlobalVariable.WSInfo.ConnectionString, "SELECT * FROM HOCSINH_LOP WHERE MaHocTap = '" + MaHocTapMoi + "'") >0 )
                            newRow["MaHocTap"] = selectedRow["MaHocTap"] + "_Copy";
                        else
                            newRow["MaHocTap"] = MaHocTapMoi;
                        newRow["SetModify"] = "I";
                    }
                    catch (Exception ex)
                    {
                        MessageBox.Show(ex.Message);
                    }
                }
                
                dt2.Rows.Add(newRow);

                
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        //Tat ca la update, ko phai addnew (cho du co chuyen di lop nao thi ma hoc tap van giu nguyen - lien quan den thu hoc phi, diem, ...)
        private void DuyetRowState(DataTable dt)
        {
            if (dt == null || dt.Rows.Count == 0)
                return;
            try
            {
                //dataTable.AcceptChanges();

                foreach (DataRow row in dt.Rows)
                    {
                        if (row["SetModify"].ToString() == "I")
                        {
                            row.AcceptChanges();
                            row.SetAdded();
                        }
                        else
                        {
                            row.AcceptChanges();
                            row.SetModified();
                        }
                    }
            }
            catch { }
        }

        private void cmdFrom2To1_Click(object sender, EventArgs e)
        {
            try
            {
                //Chuyen row dang chon tu dt1 sang dt2
                int idx = gridDSLop2.BindingContext[dt2].Position;
                DataRow selectedRow = dt2.Rows[idx];
                DataRow newRow = dt1.NewRow();
                newRow.ItemArray = selectedRow.ItemArray; // copy data
                newRow["MaLop"] = cboLop1.SelectedValue;
                newRow["MaLopCu"] = cboLop2.SelectedValue;
                newRow["SetModify"] = "U";

                if (optMove.Checked)
                {
                    dt2.Rows.Remove(selectedRow);
                }
                else  //Thay doi MaHocTap (khong la bi trung)
                {
                    try
                    {
                        //Dau tien ưu tiên lấy mã HT = Mã HS + mã Lớp, nếu bị trùng (lỗi PK) thì mới thêm chữ copy.
                        string MaHocTapMoi = selectedRow["MaHocSinh"] + cboLop1.SelectedValue.ToString();
                        if (SQLHelper.ExecuteNonQuery(GlobalVariable.WSInfo.ConnectionString, "SELECT * FROM HOCSINH_LOP WHERE MaHocTap = '" + MaHocTapMoi + "'") > 0)
                            newRow["MaHocTap"] = selectedRow["MaHocTap"] + "_Copy";
                        else
                            newRow["MaHocTap"] = MaHocTapMoi;
                        newRow["SetModify"] = "I";
                    }
                    catch (Exception ex)
                    {
                        MessageBox.Show(ex.Message);
                    }
                }
                
                dt1.Rows.Add(newRow);
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        private void cmdCapNhat_Click(object sender, EventArgs e)
        {

            try
            {
                DuyetRowState(dt1);
                DuyetRowState(dt2);

                SqlConnection cnn = new SqlConnection(GlobalVariable.WSInfo.ConnectionString);

                string sqlInsert = "INSERT INTO HOCSINH_LOP(MaHocTap, MaHocSinh, MaLop, MaTrangThai, MaCheDo, MaLopCu) " +
                                              " VALUES(@pMaHocTap, @pMaHocSinh, @pMaLop, @pMaTrangThai, @pMaCheDo, @pMaLopCu ) ";

                string sqlUpdate = "UPDATE HOCSINH_LOP SET MaLop = @pMaLop " +
                                   "WHERE MaHocTap=@pMaHocTap";

                string sqlDelete = "DELETE FROM HOCSINH_LOP  " +
                                   "WHERE MaHocTap=@pMaHocTap";
                
                SqlParameter[] pInsert = new SqlParameter[6];
                pInsert[0] = new SqlParameter("@pMaHocTap", SqlDbType.VarChar, 50, "MaHocTap");
                pInsert[1] = new SqlParameter("@pMaHocSinh", SqlDbType.VarChar, 20, "MaHocSinh");
                pInsert[2] = new SqlParameter("@pMaLop", SqlDbType.VarChar, 20, "MaLop");
                pInsert[3] = new SqlParameter("@pMaTrangThai", SqlDbType.VarChar, 2, "MaTrangThai");
                pInsert[4] = new SqlParameter("@pMaCheDo", SqlDbType.VarChar, 2, "MaCheDo");
                pInsert[5] = new SqlParameter("@pMaLopCu", SqlDbType.VarChar, 20, "MaLopCu");
                
                SqlParameter[] pUpdate = new SqlParameter[2];
                pUpdate[0] = new SqlParameter("@pMaLop", SqlDbType.VarChar, 20, "MaLop");
                pUpdate[1] = new SqlParameter("@pMaHocTap", SqlDbType.VarChar, 50, "MaHocTap");

                SqlParameter[] pDelete = new SqlParameter[1];
                pDelete[0] = new SqlParameter("@pMaHocTap", SqlDbType.VarChar, 50, "MaHocTap");

                SqlCommand cmdInsert = new SqlCommand(sqlInsert, cnn);
                SqlCommand cmdUpdate = new SqlCommand(sqlUpdate, cnn);
                SqlCommand cmdDelete = new SqlCommand(sqlDelete, cnn);

                cmdInsert.Parameters.AddRange(pInsert);
                cmdUpdate.Parameters.AddRange(pUpdate);
                cmdDelete.Parameters.AddRange(pDelete);

                //SqlDataAdapter da = new SqlDataAdapter();
                dataAdapter.InsertCommand = cmdInsert;
                dataAdapter.UpdateCommand = cmdUpdate;
                dataAdapter.DeleteCommand = cmdDelete;
                try
                {
                    dataAdapter.Update(dt1);
                    dt1.AcceptChanges();

                    dataAdapter.Update(dt2);
                    dt2.AcceptChanges();
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message);
                }

                //Load lai
                GetData(1, cboLop1.SelectedValue.ToString());
                GetData(2, cboLop2.SelectedValue.ToString());

            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            
        }

        private void cmdBoQua_Click(object sender, EventArgs e)
        {
            //Load lai
            GetData(1, cboLop1.SelectedValue.ToString());
            GetData(2, cboLop2.SelectedValue.ToString());
        }

        private void cmdIn2_Click(object sender, EventArgs e)
        {
            try
            {

                cmdIn2.Enabled = false;

                //Load report
                ReportDocument rpt = new ReportDocument();
                rpt.Load(Application.StartupPath + "\\Report\\Report_GiaoVu_KetQuaXepLop.rpt");

                //Set connection
                PublicFunction.SetDBLogonForReport(rpt);

                //Set parameter
                if (optInBenTrai.Checked)
                    PublicFunction.SetParameter(rpt, "@pMaLop", cboLop1.SelectedValue);
                else
                    PublicFunction.SetParameter(rpt, "@pMaLop", cboLop2.SelectedValue);
 
                PublicFunction.SetParameter(rpt, "@pMaTrangThai", cboTrangThai.SelectedValue );
                PublicFunction.SetParameter(rpt, "@pMaHocPhi", cboHocPhi.Text);

                //View report
                frmPrintPreview frmPP = new frmPrintPreview(rpt);
                frmPP.ShowDialog();
                frmPP.Dispose();

                rpt.Dispose();
                cmdIn2.Enabled = true;
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        

        private void label6_Click(object sender, EventArgs e)
        {

        }

        private void comboBox1_SelectedIndexChanged(object sender, EventArgs e)
        {
              
        }
        

    }
}
